//
//  CCDB.m
//  CCKit
//
//  Created by tucici on 2019/11/28.
//  Copyright © 2019 tucici. All rights reserved.
//

#import "CCDB.h"
#import <sqlite3.h>
@interface CCDB()
{
    sqlite3 *dbPoint;
}
@end
@implementation CCDB
static CCDB *_ccdb;
static dispatch_once_t ccdbOnceToken;
+ (instancetype)db;
{
    if(!_ccdb)
    {
        dispatch_once(&ccdbOnceToken, ^{
            _ccdb = [[CCDB alloc] init];
            //            [_ccdb openDM];
            //            [_ccdb createTable];
        });
    }
    return _ccdb;
}
#pragma mark == 打开数据库
- (void)openDM:(NSString *)name
{
    NSArray *sandBox=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sandBoxPath=sandBox[0];
    NSString *sqlName = [NSString stringWithFormat:@"%@.sqlite",name];
    NSString *documentPath=[sandBoxPath stringByAppendingPathComponent:sqlName];
    int result = sqlite3_open([documentPath UTF8String], &dbPoint);
    if (result ==  SQLITE_OK)
    {
        NSLog(@"数据库打开成功 = %@",documentPath);
    }
    else
    {
        NSLog(@"数据库打开失败");
    }
}

#pragma mark == 添加表格
/**
 建表 : create table if not exists 表名(字段1 约束1 约束2 约束3 ..... ,  字段2 约束1 约束2 约束3 ..... , 字段3 约束1 约束2 约束3.....)
 1. create table: 建表关键字
 2. if not exists: 如果表不存在就创建表
 3. 表名: 创建表的名字
 4. 字段名:
 5. 约束: 对字段的约束，可以有多个约束
 
 例子:
 create table if not exists stu(zfID text primary key,goodsID text,receipt text,isDid text,time text)
 */
- (void)createTable:(NSString *)sql_create
{
    if (!sql_create)
        return;
    NSString *sqlStr = @"";
    NSString *new_sql_create = [sql_create substringWithRange:NSMakeRange(sql_create.length-1, 1)];
    sqlStr = [new_sql_create stringByAppendingString:@",time text)"];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK)
    {
        NSLog(@"表创建成功");
    }
    else
    {
        NSLog(@"表创建失败");
    }
}

#pragma mark == 插入数据
- (void)insert:(NSString *)tableName withArray:(NSArray *)array
{
    
}

#pragma mark == 插入数据
/**
 插入 : insert into 表名(字段1, 字段2, 字段3.....) values (字段1值, 字段2值,  字段3值......)
 1. insert into: 插入语句关键字
 2. 表名: 要插入数据的表名
 3. 字段名: 要插入数据的字段
 4. values: 要插入的数据(要和字段顺序相同)
 
 例子:
 insert into stu(stu_name,stu_age)values(‘YYY’,30);
 */- (void)insert:(NSString *)sql_insert
{
    sqlite3_stmt *stmt = nil;
    
    int result=sqlite3_exec(self->dbPoint, [sql_insert UTF8String],nil, &stmt, nil);
    if (result==SQLITE_OK)
    {
        if (SQLITE_DONE == sqlite3_step(stmt))
        {
            NSLog(@"添加学生成功");
        }
    }
    else
    {
        NSLog(@"添加学生失败!!!!!");
    }
}
#pragma mark == 删除数据
/**
 删除 : delete from 表名  where 条件
 1. delete from: 删除语句关键字
 2. 表名: 要删除数据的表名
 3. where: 条件关键字
 4. 条件: 删除条件
 
 例子:
 delete from stu where su_age=10;
 */
- (void)delet:(NSString *)sql_delet
{
    int result=sqlite3_exec(self->dbPoint, [sql_delet UTF8String], nil, nil, nil);
    if (result==SQLITE_OK)
    {
        NSLog(@"删除成功");
    }
    else
    {
        NSLog(@"删除失败");
    }
}

#pragma mark == 更改数据
/**
 更新 : update 表名 set 字段1=修改值1, 字段2=修改值2, 字段3=修改值3  where 条件
 1. update set: 更新语句关键字
 2. 表名: 要更新数据的表名
 3. 字段名/修改值: 要修改字段和修改后的值
 4. where: 条件关键字
 5. 条件: 可以一个或者多个条件(多个条件用     and(与)，or(或)     连接)
 
 例子:
 update stu set su_age=100 where stu_age=10;
 */
- (void)update:(NSString *)sql_update
{
    int result=sqlite3_exec(self->dbPoint, [sql_update UTF8String], nil, nil, nil);
    if (result==SQLITE_OK)
    {
        NSLog(@"更新成功");
    }
    else
    {
        NSLog(@"更新失败");
        NSLog(@"%d",result);
    }
}

#pragma mark == 查询数据
/**
 查找 : select 要查找字段  from 表名  where 条件
 1. select: 查找语句关键字
 2. 要查找的字段: 如果查找所有字段可以使用通配符  *
 3. from: 关键字
 4. 表名: 要查找的数据表名
 5. where: 条件关键字
 6. 条件: 查找条件
 
 例子:
 select *from stu where su_sex = ‘XXX’;
 select stu_name,su_age from stu where s_sex=’男’;
 */
- (NSMutableArray *)select:(NSString *)sql_select
{
    sqlite3_stmt *stmt=nil;
    
    //开始查询，查询结果以dataArr返回
    NSMutableArray *dataArr = [NSMutableArray array];
    int result=sqlite3_prepare_v2(self->dbPoint, [sql_select UTF8String], -1, &stmt, nil);
    if (result==SQLITE_OK)
    {
        NSLog(@"查询成功");
        //遍历横向数据
        while (sqlite3_step(stmt)==SQLITE_ROW)
        {
            //遍历纵向数据(获取字段名)
            int i = 0;
            NSMutableDictionary *dict = [NSMutableDictionary dictionary];
            while (sqlite3_column_name(stmt, i))
            {
                const unsigned char *key_char = sqlite3_column_name(stmt, i);
                const unsigned char *value_char = sqlite3_column_text(stmt, i);
                NSString *key = [NSString stringWithUTF8String:(const char *)key_char];
                NSString *value = @"";
                
                if ((const char *)value_char)
                    value = [NSString stringWithUTF8String:(const char *)value_char];
                else
                    value = @"";
                [dict setValue:value forKey:key];//用字典包装字段名和对应的数据
                i++;
            }
            [dataArr addObject:dict];
        }
        return dataArr;
    }
    else
    {
        NSLog(@"查询失败");
        NSLog(@"%d",result);
    }
    return nil;
}
#pragma mark == 关闭数据库
- (void)closeDB
{
    int result=sqlite3_close(dbPoint);
    if (result==SQLITE_OK)
    {
        NSLog(@"数据库关闭成功");
    }
    else
    {
        NSLog(@"数据库关闭失败");
    }
}




- (void)createTable:(NSString *)tableName withOptions:(NSArray *)array
{

    NSString *optionsStr = @"";
    for (NSString *string in array)
    {
        NSString *tempString = string;
        tempString = [tempString stringByAppendingString:[tempString hasPrefix:array[0]]?@" text primary key,":@" text,"];
        optionsStr = [optionsStr stringByAppendingString:tempString];
    }

    optionsStr = [optionsStr stringByAppendingString:@"time text"];
    NSString *sqlStr=[NSString stringWithFormat:@" create table if not exists %@(%@)",tableName,optionsStr];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result==SQLITE_OK)
    {
        NSLog(@"表创建成功");
    }
    else
    {
        NSLog(@"表创建失败");
    }
}
/**
 插入 : insert into 表名(字段1, 字段2, 字段3.....) values (字段1值, 字段2值,  字段3值......)
 1. insert into: 插入语句关键字
 2. 表名: 要插入数据的表名
 3. 字段名: 要插入数据的字段
 4. values: 要插入的数据(要和字段顺序相同)

 例子:
 insert into stu(stu_name,stu_age)values(‘YYY’,30);
 */
#pragma mark == 插入数据
- (void)insert:(NSString *)tableName withDict:(NSDictionary *)dict
{
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        NSString *keyStr = @"";
        NSString *valuesStr = @"";

        NSMutableDictionary *dataDict = [NSMutableDictionary dictionary];
        for (NSString *key in [dict allKeys])
        {
           id value = [dict valueForKey:key];
            if ([value isKindOfClass:[NSArray class]])
            {

            }

            if ([[dict valueForKey:key] isKindOfClass:[NSData class]])
                [dataDict setValue:[dict valueForKey:key] forKey:key];
            else
            {
                keyStr = [keyStr stringByAppendingFormat:@"%@,",key];
                valuesStr = [valuesStr stringByAppendingFormat:@"'%@',",[dict valueForKey:key]];
            }
        }
        keyStr = [keyStr stringByAppendingString:@"time"];
        valuesStr = [valuesStr stringByAppendingFormat:@"'%@'",[self timeStamp]];

        //        [self insertData:dataDict withName:tableName];

        NSString *sqlStr=[NSString stringWithFormat:@"insert into %@ (%@) values (%@)",tableName,keyStr,valuesStr];
        sqlite3_stmt *stmt = nil;

        int result=sqlite3_exec(self->dbPoint, [sqlStr UTF8String],nil, &stmt, nil);
        if (result==SQLITE_OK)
        {
            if (SQLITE_DONE == sqlite3_step(stmt))
            {
                NSLog(@"添加学生成功");
            }
        }
        else
        {
            NSLog(@"添加学生失败!!!!!");
        }
    });
}


- (void)insertData:(NSDictionary *)dataDict withName:(NSString *)tableName
{
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{

        for (NSString *key in [dataDict allKeys])
        {
            NSData *data = [dataDict valueForKey:key];
            //插入语句
            NSString *sqlStr = [NSString stringWithFormat:@"insert into %@ (%@) values (?)",tableName, key];
            sqlite3_stmt *stmt = nil;
            int result = sqlite3_prepare_v2(self->dbPoint, [sqlStr UTF8String], -1, &stmt, nil);

            if (result == SQLITE_OK)
            {
                sqlite3_bind_text(stmt, 1, [sqlStr UTF8String], -1, NULL);

                // 用data 调用bytes函数，并且获得长度。这里的长度一定要设置，不然的话数据会不正确
                sqlite3_bind_blob(stmt, 2, [data bytes], (int)[data length] , NULL);
                //好了，这一步就绑定了，是不是也挺简单的

                if (SQLITE_DONE==sqlite3_step(stmt))
                {
                    NSLog(@"添加data成功");
                }
            }
        }
    });
}
/**
 更新 : update 表名 set 字段1=修改值1, 字段2=修改值2, 字段3=修改值3  where 条件
 1. update set: 更新语句关键字
 2. 表名: 要更新数据的表名
 3. 字段名/修改值: 要修改字段和修改后的值
 4. where: 条件关键字
 5. 条件: 可以一个或者多个条件(多个条件用     and(与)，or(或)     连接)

 例子:
 update stu set su_age=100 where stu_age=10;
 */
- (void)update:(NSString *)tableName withDict:(NSDictionary *)dict conditionsDict:(NSDictionary*)conditionsDict
{

    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        NSString *string = @"";
        for (NSString *key in [dict allKeys])
        {
            string = [string stringByAppendingFormat:@"%@='%@',",key,[dict valueForKey:key]];
        }
        string = [string stringByAppendingFormat:@"time='%@'",[self timeStamp]];

        NSString *sqlStr= [NSString stringWithFormat:@"update %@ set %@ where %@='%@'",tableName, string,[conditionsDict allKeys][0], [conditionsDict allValues][0]];
        int result=sqlite3_exec(self->dbPoint, [sqlStr UTF8String], nil, nil, nil);
        if (result==SQLITE_OK)
        {
            NSLog(@"更新成功");
        }
        else
        {
            NSLog(@"更新失败");
            NSLog(@"%d",result);
        }
    });
}
/**
 删除 : delete from 表名  where 条件
 1. delete from: 删除语句关键字
 2. 表名: 要删除数据的表名
 3. where: 条件关键字
 4. 条件: 删除条件

 例子:
 delete from stu where su_age=10;
 */
- (void)delet:(NSString *)tableName withDict:(NSDictionary *)dict
{

    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        NSString *sqlStr=[NSString stringWithFormat:@"delete from %@ where %@='%@'",tableName, [dict allKeys][0], [dict allValues][0]];
        int result=sqlite3_exec(self->dbPoint, [sqlStr UTF8String], nil, nil, nil);
        if (result==SQLITE_OK)
        {
            NSLog(@"删除成功");
        }
        else
        {
            NSLog(@"删除失败");
        }
    });
}
/**
 查找 : select 要查找字段  from 表名  where 条件
 1. select: 查找语句关键字
 2. 要查找的字段: 如果查找所有字段可以使用通配符  *
 3. from: 关键字
 4. 表名: 要查找的数据表名
 5. where: 条件关键字
 6. 条件: 查找条件

 例子:
 select *from stu where su_sex = ‘XXX’;
 select stu_name,su_age from stu where s_sex=’男’;
 */
- (void)select:(NSString *)tableName withOptions:(nullable NSArray *)array conditionsDict:(NSDictionary*)conditionsDict complete:(void(^) (NSMutableArray *array))block
{
    dispatch_async(dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0), ^{
        NSMutableArray *dataArr = [self select:tableName withOptions:array conditionsDict:conditionsDict];
        if (block)
            block(dataArr);

    });
}
- (NSMutableArray *)select:(NSString *)tableName withOptions:(nullable NSArray *)array conditionsDict:(NSDictionary*)conditionsDict
{

    //遍历字段数组，判断是否有字段，没有制定的字段，就使用通配符*
    NSString *string = @"";
    if (array)
    {
        for (NSString *subStr in array)
        {
            string = [string stringByAppendingFormat:@"%@,",subStr];
            string = [string substringToIndex:string.length-1];
        }
    }
    string = string.length>0?string:@"*";

    //拼接查询语句
    NSString *sqlStr=@"";
    if (conditionsDict == nil || conditionsDict.allKeys.count <= 0) {
        sqlStr=[NSString stringWithFormat:@"select %@ from %@",string, tableName];
    }else{
        sqlStr=[NSString stringWithFormat:@"select %@ from %@ where %@='%@'",string, tableName, [conditionsDict allKeys][0], [conditionsDict allValues][0]];
    }
    sqlite3_stmt *stmt=nil;

    //开始查询，查询结果以dataArr返回
    NSMutableArray *dataArr = [NSMutableArray array];
    int result=sqlite3_prepare_v2(self->dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    if (result==SQLITE_OK)
    {
        NSLog(@"查询成功");
        //遍历横向数据
        while (sqlite3_step(stmt)==SQLITE_ROW)
        {
            //遍历纵向数据(获取字段名)
            int i = 0;
            NSMutableDictionary *dict = [NSMutableDictionary dictionary];
            while (sqlite3_column_name(stmt, i))
            {
                const unsigned char *key_char = sqlite3_column_name(stmt, i);
                const unsigned char *value_char = sqlite3_column_text(stmt, i);
                NSString *key = [NSString stringWithUTF8String:(const char *)key_char];
                NSString *value = @"";

                if ((const char *)value_char)
                    value = [NSString stringWithUTF8String:(const char *)value_char];
                else
                    value = @"";
                [dict setValue:value forKey:key];//用字典包装字段名和对应的数据
                i++;
            }
            [dataArr addObject:dict];
        }
        return dataArr;
    }
    else
    {
        NSLog(@"查询失败");
        NSLog(@"%d",result);
    }
    return nil;
}
- (NSString*)timeStamp
{
    NSString *date = [NSString stringWithFormat:@"%f",[[NSDate date] timeIntervalSince1970]];
    return [date componentsSeparatedByString:@"."][0];
}
@end
